package rpgsdb.db;

import core.sb.SB;
import core.sb.s.Seq;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/*Author: John Archer, Contact: john.archer.jpa@gmail.com*/
public class RPGSDBWrapper extends RPGSDB {
    
    public RPGSDBWrapper(boolean newdb){super(newdb);}
    
    private PreparedStatement getProjs,addProj, getProj, rmProj;
    private PreparedStatement addData, getDataNo, getDataByPID, getDID;
    private PreparedStatement getDataPath;
    private PreparedStatement rmDataByDID, rmDataPID;
    private PreparedStatement getRef;

    private void prepareStatements(Connection con){
        try{
            getProjs = con.prepareStatement("SELECT * FROM APP.PROJECT");
            rmProj = con.prepareStatement("DELETE FROM APP.PROJECT WHERE ID = ?");
            addProj = con.prepareStatement("INSERT INTO APP.PROJECT (" +
                    "TITLE, " +
                    "WORD_SIZE, " +
                    "READ_KMER_QUALITY, " +
                    "WORD_SKIP, " +
                    "MATCH_SCORE, " +
                    "GAP_OPEN_SCORE, " +
                    "GAP_EXTENTION, " +
                    "TRANSVERSION_SCORE, " +
                    "TRANSITION_SCORE, " +
                    "MINIMUM_READ_LENGTH_TO_KEEP, " +
                    "MAXIMUM_READ_LENGTH_TO_KEEP, " +
                    "MAX_INDEL_SIZE_TO_COUNT, " +
                    "TEMPLATE_TITLE, " +
                    "TEMPLATE " +
                    ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            getProj = con.prepareStatement("SELECT * FROM APP.PROJECT WHERE TITLE = ?");
            getRef = con.prepareStatement("SELECT TEMPLATE_TITLE, TEMPLATE FROM APP.PROJECT WHERE ID = ?");
            //Datasets table
            addData = con.prepareStatement("INSERT INTO APP.DATASET (TITLE, TITLE_PATH, PROJECT_ID) VALUES (?,?,?)");
            rmDataByDID = con.prepareStatement("DELETE FROM APP.DATASET WHERE ID = ?");
            rmDataPID= con.prepareStatement("DELETE FROM APP.DATASET WHERE PROJECT_ID = ?");
            getDataNo = con.prepareStatement("SELECT COUNT(*) AS counter FROM APP.DATASET WHERE TITLE = ? AND PROJECT_ID = ?");
            getDID = con.prepareStatement("SELECT * FROM APP.DATASET WHERE TITLE = ? AND PROJECT_ID = ?");
            getDataPath = con.prepareStatement("SELECT TITLE_PATH FROM APP.DATASET WHERE ID = ?");
            getDataByPID = con.prepareStatement("SELECT * FROM APP.DATASET WHERE PROJECT_ID = ?");
        }
        catch(Exception e){e.printStackTrace();}
    }
    
    private boolean connected = false;
    public boolean connect(){
        String dbUrl = getDatabaseUrl();
        try {
            con = DriverManager.getConnection(dbUrl, dbProps);
            prepareStatements(con);
            connected = con != null;
        }
        catch(SQLException ex){connected = false;ex.printStackTrace();}
        return connected;
    }

    public void disconnect() {
        if(connected) {
            String dbUrl = getDatabaseUrl();
            dbProps.put("shutdown", "true");
            try {DriverManager.getConnection(dbUrl, dbProps);}
            catch(SQLException ex){}
            connected = false;
        }
    }
    
    public int addProject(
            String title,
            int WORD_SIZE,
            int READ_KMER_QUALITY,
            int WORD_SKIP,
            double MATCH_SCORE,
            double GAP_OPEN_SCORE,
            double GAP_EXTENTION,
            double TRANSVERSION_SCORE,
            double TRANSITION_SCORE,
            int MINIMUM_READ_LENGTH_TO_KEEP,
            int MAXIMUM_READ_LENGTH_TO_KEEP,
            int MAX_INDEL_SIZE_TO_COUNT,
            String TEMPLATE_TITLE,
            String TEMPLATE
            ) throws Exception{

        addProj.clearParameters();
        addProj.setString(1, title);
        addProj.setInt(2, WORD_SIZE);
        addProj.setInt(3, READ_KMER_QUALITY);
        addProj.setInt(4, WORD_SKIP);
        addProj.setDouble(5, MATCH_SCORE);
        addProj.setDouble(6, GAP_OPEN_SCORE);
        addProj.setDouble(7, GAP_EXTENTION);
        addProj.setDouble(8, TRANSVERSION_SCORE);
        addProj.setDouble(9, TRANSITION_SCORE);
        addProj.setInt(10, MINIMUM_READ_LENGTH_TO_KEEP);
        addProj.setInt(11, MAXIMUM_READ_LENGTH_TO_KEEP);
        addProj.setInt(12, MAX_INDEL_SIZE_TO_COUNT);
        addProj.setString(13, TEMPLATE_TITLE);
        addProj.setString(14, TEMPLATE);
        addProj.executeUpdate();

        int projectID = -1;
        getProj.clearParameters();
        getProj.setString(1, title);
        ResultSet result = getProj.executeQuery();
        
        result.next();
        projectID = result.getInt("ID");
        return projectID;
    }

    public void rmProj(int pid) throws Exception{
        rmProj.clearParameters();
        rmProj.setInt(1, pid);
        rmProj.executeUpdate();
    }

    public SB getRef(int pid) throws Exception{
        SB ref = new SB();
        getRef.clearParameters();
        getRef.setInt(1, pid);
        ResultSet results = getRef.executeQuery();
        while(results.next()) {
            String title = results.getString("TEMPLATE_TITLE");
            String sequence = results.getString("TEMPLATE");
            ref.add(new Seq(title, sequence));
        }
        return ref;
    }

    public ArrayList <NK> getProjNK(){
        ArrayList <NK> namesAndKeys = new ArrayList();
            try{
            getProjs.clearParameters();
            ResultSet results = getProjs.executeQuery();
            while(results.next()) {
                int id = results.getInt("ID");
                String title = results.getString("TITLE");
                namesAndKeys.add(new NK(title, id));
            }
        }
        catch(Exception e){}
        return namesAndKeys;
    }

    public int addData(String title, String pathPlusTitle, int proj_id) throws Exception{
        boolean exists = false;
            getDataNo.clearParameters();
            getDataNo.setString(1, title);
            getDataNo.setInt(2, proj_id);
            ResultSet result = getDataNo.executeQuery();
            result.next();
            int count = result.getInt("counter");
            if(count==0){exists = false;}
            else{exists = true;}

        if(exists==false){
            addData.clearParameters();
            addData.setString(1, title);
            addData.setString(2, pathPlusTitle);
            addData.setInt(3, proj_id);
            addData.executeUpdate();
        }
            
        getDID.clearParameters();
        getDID.setString(1, title);
        getDID.setInt(2, proj_id);
        
        ResultSet result2 = getDID.executeQuery();
        result2.next();
        return result2.getInt("ID");
    }

    public void rmDataByDID(int DATASET_ID) throws Exception{
        rmDataByDID.clearParameters();
        rmDataByDID.setInt(1, DATASET_ID);
        rmDataByDID.executeUpdate();
    }

    public void rmDataByPID(int PROJECT_ID) throws Exception{
        rmDataPID.clearParameters();
        rmDataPID.setInt(1, PROJECT_ID);
        rmDataPID.executeUpdate();
    }

    public String getPath(int did) throws Exception{
        getDataPath.clearParameters();
        getDataPath.setInt(1, did);
        ResultSet result2 = getDataPath.executeQuery();
        result2.next();
        return result2.getString("TITLE_PATH");
    }

    public ArrayList <NK> getDataNK(int pid){
        ArrayList <NK> nk = new ArrayList();
        try{
            getDataByPID.clearParameters();
            getDataByPID.setInt(1, pid);
            ResultSet results = getDataByPID.executeQuery();
            while(results.next()) {
                int id = results.getInt("ID");
                String title = results.getString("TITLE");
                nk.add(new NK(title, id));
            }
        }catch(Exception e){}
        return nk;
    }
}